<?php
ini_set('memory_limit', '-1');
ini_set('max_execution_time', 86400);
           
// Set flag that this is a parent file.
define('_JEXEC', 1);
define('DS', DIRECTORY_SEPARATOR);

if (file_exists(dirname(__FILE__) . '/defines.php')) {
	include_once dirname(__FILE__) . '/defines.php';
}

if (!defined('_JDEFINES')) {
	define('JPATH_BASE', dirname(__FILE__));
	require_once JPATH_BASE.'/includes/defines.php';
}
require_once JPATH_BASE.'/includes/framework.php';

// Instantiate the application.
$app = JFactory::getApplication('site');

// Initialise the application.
$app->initialise();
         
// Return the response.
require_once JPATH_BASE.DS.'administrator'.DS.'components'.DS.'com_wrd'.DS.'classes'.DS.'utilities'.DS.'reportProvider.php';
require_once JPATH_BASE.DS.'administrator'.DS.'components'.DS.'com_wrd'.DS.'classes'.DS.'utilities'.DS.'pointProvider.php';
require_once JPATH_BASE.DS.'administrator'.DS.'components'.DS.'com_wrd'.DS.'classes'.DS.'PHPExcel.php';

$objPHPExcel = new PHPExcel();
$objPHPExcel->getProperties()->setCreator("Wmc")
                             ->setLastModifiedBy("Wmc")
                             ->setTitle("Transaction Report")
                             ->setSubject("Transaction Report")
                             ->setDescription("Transaction Report")
                             ->setKeywords("Transaction Report")
                             ->setCategory("Transaction Report");


// Create a first sheet, representing sales data
$objPHPExcel->setActiveSheetIndex(0);
$activeSheet = $objPHPExcel->getActiveSheet();
$activeSheet->mergeCells('A1:M1');
$activeSheet->setCellValue('A1', 'Member Spending Report');
$activeSheet->getStyle('A1')->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
$activeSheet->getStyle('A1')->getFont()->setSize(24);
$activeSheet->getStyle('A1')->getFont()->setBold(true);

$activeSheet->setCellValue('A3', 'Member No');
$activeSheet->setCellValue('B3', 'Member Name');
$activeSheet->setCellValue('C3', 'Check #');
$activeSheet->setCellValue('D3', 'Date');
$activeSheet->setCellValue('E3', 'Outlet');
$activeSheet->setCellValue('F3', 'Pax');

$activeSheet->setCellValue('G3', 'Loyalty Code');
$activeSheet->setCellValue('H3', 'Total Spend');
$activeSheet->setCellValue('I3', 'Food');
$activeSheet->setCellValue('J3', 'Beverage');
$activeSheet->setCellValue('K3', 'Liquor');
$activeSheet->setCellValue('L3', 'Other');
$activeSheet->setCellValue('M3', 'Points');

$cols = array('A', 'B', 'C', 'D', 'E', 'F', 'G', 'H', 'I', 'J', 'K', 'L', 'M');
foreach($cols as $item)
{
    if($item == 'B')
    {
        setBackgroundColor($activeSheet, $item, 3, 25);
    }
    else if($item == 'E')
    {
        setBackgroundColor($activeSheet, $item, 3, 30);
    }
    else
    {
        setBackgroundColor($activeSheet, $item, 3);   
    }
}

/************* Query Data *************/

$query = "
    SELECT
      `t`.id             AS `id`,
      `t`.pax            AS `pax`,
      `t`.total          AS `total`,
      `t`.beverage       AS `beverage`,
      `t`.food           AS `food`,
      `t`.vip_number     AS `vip_number`,
      `t`.other          AS `other`,
      `t`.vip_number     AS `vip_number`,
      `t`.transaction_id AS `check`,
      `t`.liquor         AS `liquor`,
      `t`.date_receipt   AS `date`,
      `p`.f_name         AS `f_name`,
      `p`.l_name         AS `l_name`,
      `o`.NAME           AS `outlet`
    FROM vip_wrd_transaction `t`
      LEFT JOIN #__wrd_outlets `o`
        ON `o`.code = `t`.outlet
      LEFT JOIN #__wrd_profile `p`
        ON `p`.member_id = `t`.vip_number
    WHERE `convert` = 1
    LIMIT 0, 10
";
$db = JFactory::getDbo();
$db->setQuery($query);
$data = $db->loadObjectList();
 
if(!empty($data))
{
    $transaction_id = '';
    $size = count($data);
    for($i=0; $i<$size; $i++)
    {
        $transaction_id .= $data[$i]->check.',';
    }
    $transaction_id = trim($transaction_id, ',');
    $query = 'SELECT `point`, `transaction` FROM #__wrd_point WHERE `transaction` IN ('.$transaction_id.')';
    $db->setQuery($query) ;
    $point = $db->loadObjectList();
    if(!empty($point))
    {
        $index = 4;
        foreach($point as $item)
        {
            for($i=0; $i<$size; $i++)
            {
                $activeSheet->setCellValue('A'.$index, 'V'.$data[$i]->vip_number);
                $activeSheet->getStyle('A'.$index)->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
                
                $activeSheet->setCellValue('B'.$index, $data[$i]->l_name.' '.$data[$i]->f_name);
                $activeSheet->setCellValue('C'.$index, $data[$i]->check);
                $activeSheet->getStyle('C'.$index)->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
                
                $data[$i]->date = explode(' ', $data[$i]->date);
                $activeSheet->setCellValue('D'.$index, $data[$i]->date[0]);
                $activeSheet->getStyle('D'.$index)->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
                
                $activeSheet->setCellValue('E'.$index, $data[$i]->outlet);
                //$activeSheet->getStyle('E'.$index)->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
                
                $activeSheet->setCellValue('F'.$index, $data[$i]->pax);
                $activeSheet->getStyle('F'.$index)->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
                
                $activeSheet->setCellValue('G'.$index, '');
                $activeSheet->setCellValue('H'.$index, $data[$i]->total);
                $activeSheet->setCellValue('I'.$index, $data[$i]->food);
                $activeSheet->setCellValue('J'.$index, $data[$i]->beverage);
                $activeSheet->setCellValue('K'.$index, $data[$i]->liquor);
                $activeSheet->setCellValue('L'.$index, $data[$i]->other);
                
                if($data[$i]->check == $item->transaction)
                {
                    $activeSheet->setCellValue('M'.$index, $item->point);
                    break;
                }
            }
            $index++;
        }
    }
    
}
$fileName = md5(microtime().'_wmcreward');
$objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel2007');
$objWriter->save(JPATH_BASE.DS.'tmp'.DS.$fileName.'.xlsx');

$fullPath = JPATH_ROOT.DS.'tmp'.DS.$fileName.'.xlsx';
$fsize = filesize($fullPath);
                
header("Pragma: public"); // required
header("Expires: 0");
header("Cache-Control: must-revalidate, post-check=0, pre-check=0");
header("Cache-Control: private",false); // required for certain browsers
header('Content-Type: application/vnd.ms-excel;');           
header("Content-type: application/x-msexcel"); 
header("Content-Disposition: attachment; filename=\"wmc.xlsx\";" );
header("Content-Transfer-Encoding: binary");
header("Content-Length: ".$fsize);
ob_clean();
flush();
readfile( $fullPath );
exit;

function setBackgroundColor(&$activeSheet, $colName, $colIndex, $width=15)
{
    $fill = $activeSheet->getStyle($colName.$colIndex)->getFill();
    $fill->setFillType(PHPExcel_Style_Fill::FILL_SOLID);
    $fill->getStartColor()->setARGB('cc99ff');  
    $activeSheet->getColumnDimension($colName)->setWidth($width);
    
    $style = $activeSheet->getStyle($colName.$colIndex);
    $style->getFont()->setSize(12);
    $style->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
}